﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;


public partial class CreateTable : System.Web.UI.Page
{
    DataSet DS;
    protected void Page_Load(object sender, EventArgs e)
    {

        show();
    }

    private void show()
    {
        //ReadExeclData xlsData = new ReadExeclData();
        //DS = xlsData.GetEceclData();

        //if (DS.Tables[0].Rows.Count > 0)
        //{

        //    gvExecl.DataSource = DS.Tables[0];
        //    gvExecl.DataBind();
        //}
    }
    
    protected void btnCreate_Click(object sender, EventArgs e)
    {
        string TableName = tbTbnm.Text;
        string sql="";
        string Clumn="";
        string PK="";
        string ExtendedProperty = "";
        for (int i = 0; i < DS.Tables[0].Rows.Count; i++)
        {
            //欄位
            DataRow dr = DS.Tables[0].NewRow();
            dr = DS.Tables[0].Rows[i];
            // column name, type , null, default, 
            Clumn += string.Format("[{0}] {1} {2} {3}", dr[1],setDBType(dr[2],dr[3]), checkNull(dr[4]),setDefault(TableName,dr[1],dr[5],dr[2]));
            Clumn += i == ((DS.Tables[0].Rows.Count) - 1) ? "\n" : ", \n";
            //主鍵
           
            if (checkPK(dr[0]))
            {
                if (PK.Length!=0)
                    PK += ", \n";
                else
                    PK += "\n";

                PK += string.Format("[{0}] ASC", (dr[1])) ;
            }
            //欄位說明
            if (dr[6] != null || dr[7]!=null)
            {
                ExtendedProperty += string.Format("GO \n EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}{1}' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'{2}', @level2type=N'COLUMN', @level2name=N'{3}'", dr[6], dr[7], TableName, dr[1]);
                ExtendedProperty += "\n";
            }
            
        }

        sql = ReadTxtFile();
        //Response.Write(sql);
       // 
        sql = sql.Replace("##TableName##", TableName);
        sql = sql.Replace("##Colums##", Clumn);
        sql = sql.Replace("##PK##", PK);
        sql = sql.Replace("##ExtendedProperty##", ExtendedProperty);

        TextBox1.Text = sql;
        //showplace.Text = sql;

        //SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["masterDB"].ConnectionString);
        //SqlCommand myCommand = new SqlCommand(TextBox1.Text, myConnection);
        //myCommand.CommandType = CommandType.Text;
        //myConnection.Open();
        //myCommand.ExecuteNonQuery();
        //myConnection.Close();
        
    }

    private string setDBType(object type, object len)
    {
        if (type != null)
        {
            if (type.ToString() == "char" || type.ToString() == "varchar")
            {
                if (len.ToString().Length ==0) len = "max";
                //return string.Format("[{0}] ({1}) COLLATE Chinese_Taiwan_Stroke_CI_AS ", type, len);
                return string.Format("[{0}] ({1}) ", type, len);
            }
            else if (type.ToString() == "int" || type.ToString() == "float")
            {
                return string.Format("[{0}] ", type);
            }
            else return "";
            
        }
        else return "";
    }

    private string setDefault(string tname, object cname, object p, object type)
    {
        if (p.ToString().Length>0)
        {
            if (IsText(type))
                return string.Format(" CONSTRAINT [DF_{0}_{1}]  DEFAULT (('{2}')) ", tname, cname, p);
            else 
                return string.Format(" CONSTRAINT [DF_{0}_{1}]  DEFAULT (({2})) ", tname, cname, p);
        }
        else
            return "";
    }

    private bool checkPK(object p)
    {
        if (p != null)
            return p.ToString() == "pk" ? true : false;
        else
            return false;
    }

    private string checkNull(object p)
    {
        if (p != null)
        {

            if (p.ToString() == "N")
                return "NOT NULL";
            else
                return "";
        }
        else
            return "";
    }
    
    /// <summary>
    /// 文字還是數字
    /// </summary>
    /// <returns></returns>
    private bool IsText(object type)
    {
        if (type.ToString() == "char" || type.ToString() == "varchar")  
            return true;
        else
            return false;
    }

    private string ReadTxtFile()
    {
        string txtcontent = "";
        FileInfo fi = new FileInfo(Server.MapPath("/Templet/SQLScript/Create.txt"));
        using (FileStream fs = fi.Open(FileMode.Open, FileAccess.Read))
        {
            using (StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default))
            {
                txtcontent = sr.ReadToEnd();
            }
        }
        fi = null;

        return txtcontent;
        //txtReader = File.OpenText();
    }
}
